﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Drawing;

namespace sistemaAcademicoFinanceiroEscolas
{
    public partial class uploadExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            string connectionString = "";
            if (FileUpload1.HasFile)
            {
                string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
                string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                string fileLocation = Server.MapPath("~/up/" + fileName);
                bool extensaoPermitida = false;
                bool arquivoGravado = false;

                if (FileUpload1.HasFile)
                {
                    string extensaoArquivo = Path.GetExtension(FileUpload1.FileName);

                    string[] extensoesPermitidas = { ".xls" }; //inserir apenas a extensõa .JPG



                    for (int i = 0; i < extensoesPermitidas.Length; i++)
                        if (extensoesPermitidas[i] == extensaoArquivo)
                            extensaoPermitida = true;

                    if (extensaoPermitida == true)
                    {
                        try
                        {
                            FileUpload1.PostedFile.SaveAs(fileLocation);
                            arquivoGravado = true;
                        }
                        catch (Exception ex)
                        {
                            ErroLabel.ForeColor = Color.Red;
                            ErroLabel.Text = "Ocorreu um problema na tentativa de salvar o arquivo." + ex.ToString();
                            ErroLabel.Visible = true;
                            arquivoGravado = false;
                        }
                    }
                    else
                    {
                        ErroLabel.ForeColor = Color.Red;
                        ErroLabel.Text = string.Format("Tipo de arquivo inválido. Somente sao aceitos arquivos .xls.", extensoesPermitidas.ToString());
                        ErroLabel.Visible = true;
                    }

                    if (arquivoGravado)
                    {

                        ErroLabel.ForeColor = Color.Blue;
                        ErroLabel.Text = string.Format("Arquivo gravado com sucesso.");
                        ErroLabel.Visible = true;
                    }
                }

                //Check whether file extension is xls or xslx

                if (fileExtension == ".xls")
                {
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                }
                else if (fileExtension == ".xlsx")
                {
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                }

                //Create OleDB Connection and OleDb Command

                OleDbConnection con = new OleDbConnection(connectionString);
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Connection = con;
                OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
                DataTable dtExcelRecords = new DataTable();
                con.Open();

                DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
                cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
                dAdapter.SelectCommand = cmd;
                dAdapter.Fill(dtExcelRecords);
                con.Close();

                //criação de strings para receber as linhas do excel
                string codigoAlunoExcel = "";
                string nomeAluno = "";
                string aniversario = "";
                string dataAtualizacao = "";
                string telefoneResidencial = "";
                string email = "";
                string indicacao = "";
                string serie = "";
                string colegioEscola = "";
                string bairro = "";
                string alergia = "";
                string responsavel = "";
                string celular = "";
                string reponsavel2 = "";
                string celular2 = "";
                string parentesco = "";
                string ocupacao = "";
                string foneTrabalho = "";
                string parentesco2 = "";
                string celular3 = "";
                string ocupacao2 = "";
                string logradouro = "";
                string foneTrabalho2 = "";
                string numeroTrabalho = "";
                string complemento = "";
                string bairroTrabalho = "";
                string cidadeTrabalho = "";
                string uf = "";
                string cep = "";
                string referencia = "";
                string observacoes = "";




                SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqlc.Open();
                SqlConnection sqld = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqld.Open();




                foreach (DataRow row in dtExcelRecords.Rows) // Loop over the rows.
                {
                    //Console.WriteLine("--- Row ---"); // Print separator.
                    //foreach (var item in row.ItemArray) // Loop over the items.
                    //{
                    //    Console.Write("Item: "); // Print label.
                    //    Console.WriteLine(item); // Invokes ToString abstract method.


                    //    // string recebendo o valor do excel e row é o numero da coluna.                        


                    //}

                    codigoAlunoExcel = row[0].ToString();
                    nomeAluno = row[1].ToString();
                    telefoneResidencial = row[2].ToString();                    
                    dataAtualizacao = row[4].ToString();
                    aniversario = row[4].ToString();
                    email = row[6].ToString();
                    indicacao = row[7].ToString();
                    serie = row[8].ToString();
                    colegioEscola = row[9].ToString();
                    bairro = row[10].ToString();
                    alergia = row[11].ToString();
                    responsavel = row[3].ToString();
                    celular = row[5].ToString();
                    reponsavel2 = row[4].ToString();
                    celular2 = row[6].ToString();
                    parentesco = row[13].ToString();
                    ocupacao = row[15].ToString();
                    foneTrabalho = row[16].ToString();
                    parentesco2 = row[18].ToString();
                    celular3 = row[19].ToString();
                    ocupacao2 = row[20].ToString();
                    logradouro = row[21].ToString();
                    foneTrabalho2 = row[22].ToString();
                    numeroTrabalho = row[23].ToString();
                    complemento = row[24].ToString();
                    bairroTrabalho = row[25].ToString();
                    cidadeTrabalho = row[26].ToString();
                    uf = row[27].ToString();
                    cep = row[28].ToString();
                    referencia = row[29].ToString();
                    observacoes = row[30].ToString();


                    if (nomeAluno == "")
                    {
                        break;
                    }

                    //1. fazer um select na tabela aluno pesquisando se o nome do aluno ja se encontra cadastrado  
                    string sqlVerificar = "select * from aluno where nomeAluno = '" + nomeAluno + "' order by nomeAluno";

                    SqlCommand cmdAluno = new SqlCommand(sqlVerificar, sqlc);
                    SqlDataReader myReaderVerificar = cmdAluno.ExecuteReader();

                    

                    if (myReaderVerificar.HasRows)
                    {
                        myReaderVerificar.Read();
                        //o aluno existe na tabela, realizar update do aluno

                        string sqlupdate = "update aluno set codigoAlunoExcel = " + codigoAlunoExcel + ", nomeAluno= '" + nomeAluno + "', dataNascimentoAluno= '";
                        sqlupdate = sqlupdate + aniversario + "', ruaResidenciaAluno= '" + logradouro + "', bairroResidenciaAluno= '";
                        sqlupdate = sqlupdate + bairro + "', telefoneResidenciaAluno= '" + telefoneResidencial + "', celularAluno= '";
                        sqlupdate = sqlupdate + celular + "', emailAluno= '" + email + "', nomePaiAluno= '" + responsavel + "', profissaoPaiAluno= '";
                        sqlupdate = sqlupdate + ocupacao + "', telefoneEmpresaPaiAluno= '" + foneTrabalho + "', nomeMaeAluno= '" + reponsavel2 + "', celularMaeAluno= '";
                        sqlupdate = sqlupdate + celular2 + "', profissaoMaeAluno= '" + ocupacao2 + "', telefoneEmpresaMaeAluno= '" + foneTrabalho2 + "', observacoesAluno= '";
                        sqlupdate = sqlupdate + observacoes + "', alergiaAluno= '" + alergia + "',escolaAluno= '" + colegioEscola + "', indicacaoAluno= '";
                        sqlupdate = sqlupdate + indicacao + "' where codigoAluno = " + myReaderVerificar["codigoAluno"].ToString();                        


                        SqlCommand updateAluno = new SqlCommand(sqlupdate, sqld);
                        updateAluno.ExecuteNonQuery();

                    }else{

                        string inserir2 = "";

                        inserir2 += "INSERT INTO aluno (codigoAlunoExcel, nomeAluno, telefoneResidenciaAluno , ruaResidenciaAluno, ";
                        inserir2 += "bairroResidenciaAluno, dataNascimentoAluno , celularAluno, emailAluno, ";
                        inserir2 += "nomePaiAluno, celularPaiAluno, profissaoPaiAluno, telefoneEmpresaPaiAluno, ";
                        inserir2 += "nomeMaeAluno, celularMaeAluno, profissaoMaeAluno, ";
                        inserir2 += "telefoneEmpresaMaeAluno, observacoesAluno, alergiaAluno, escolaAluno, ";
                        inserir2 += "indicacaoAluno) ";
                        inserir2 += "VALUES ('" + codigoAlunoExcel + "','" + nomeAluno + "', '" + telefoneResidencial + "', '" + logradouro + "', ";
                        inserir2 += "'" + bairro + "', '" + aniversario + "', '" + celular + "', '" + email + "', ";
                        inserir2 += "'" + responsavel + "', '" + celular2 + "','" + ocupacao + "', '" + foneTrabalho + "', '" + reponsavel2 + "', ";
                        inserir2 += "'" + celular3 + "', '" + ocupacao2 + "', '" + foneTrabalho2 + "', '" + observacoes + "', ";
                        inserir2 += "'" + alergia + "', '" + colegioEscola + "', '" + indicacao + "')";
                        

                        SqlCommand inserirAluno = new SqlCommand(inserir2, sqld);
                        inserirAluno.ExecuteNonQuery();
                        //break;                
                    
                    
                    
                    }

                    myReaderVerificar.Close();

                    //aqui

                    string literalc = "<script language=JavaScript>finalizar();</script>";
                    form1.Controls.Add(new LiteralControl(literalc));
                }

                sqlc.Close();
                sqld.Close();

            }
        }
    }
}